# coding=utf-8
from scrapy_redis_test.douban_group_spider.mysql_process import mysql_connection_pool
from redis import StrictRedis,ConnectionPool
import json


redis_settings = {
    "REDIS_HOST" : "192.168.1.130",
    "REDIS_PORT" : "9901",
    "REDIS_DB" : "0",
    "REDIS_URL" : "redis://@192.168.1.130:9901/0"
}
#配置redis数据库

class MysqlProcess(object) :
    def __init__(self):
        #开启redis连接池
        self.redis_pool = ConnectionPool.from_url(redis_settings["REDIS_URL"])
        self.redis = StrictRedis(connection_pool=self.redis_pool)

    def get_redis_item(self):
        source, data = self.redis.blpop("spider:items")
        item = json.loads(data)
        return item

    def get_insert_sql(self, item):
        check = item["item"]
        if check == "TopicItem":
            sql = """INSERT INTO `topic` (`id`, `title`, `person_name`, `content`, `group_id`) VALUES (%s,%s,%s,%s,%s)"""
        elif check == "GroupItem":
            sql = """INSERT INTO `group` (`id`, `name`, `leader`, `content`, `time`) VALUES (%s,%s,%s,%s,%s)"""
        elif check == "CommentItem":
            sql = """INSERT INTO `comment` (`id`, `person_name`, `content`, `topic_id`) VALUES (%s,%s,%s,%s)"""
        else :
            sql = None
        return sql

    def get_insert_data(self, item):
        check = item["item"]
        if check == "TopicItem":
            args = (item["id"], item["title"], item["person_name"], item["content"], item["group_id"])
        elif check == "GroupItem":
            args = (item["id"], item["name"], item["leader"], item["content"], item["time"])
        elif check == "CommentItem":
            args = (item["id"], item["person_name"], item["content"], item["topic_id"])
        else :
            args = None
        return args

    def process(self, items):
        with mysql_connection_pool.getPTConnection() as db:
            try:
                cursor = db.cursor
                con = db.conn
                sql = self.get_insert_sql(item)
                args = self.get_insert_data(item)
                if sql and args:
                    print(sql)
                    print(args)
                    cursor.execute(sql, args)
                    con.commit()
            except Exception as e:
                print(e)


    def close(self):
        self.redis_pool.disconnect()

if __name__ == '__main__':
    process = MysqlProcess()
    flag = True
    while flag:
        item = process.get_redis_item()
        if item:
            process.process(item)
        else:
            flag = False
    process.close()